{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import auto_everything"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting Pillow\n",
      "  Using cached Pillow-8.3.1-cp39-cp39-macosx_10_10_x86_64.whl (2.9 MB)\n",
      "Installing collected packages: Pillow\n",
      "Successfully installed Pillow-8.3.1\n",
      "\u001b[33mWARNING: You are using pip version 21.1.3; however, version 21.2.4 is available.\n",
      "You should consider upgrading via the '/usr/local/opt/python@3.9/bin/python3.9 -m pip install --upgrade pip' command.\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "!python3 -m pip install --upgrade Pillow"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from PIL import Image\n",
    "from io import BytesIO\n",
    "from base64 import b64decode\n",
    "\n",
    "from auto_everything.disk import Disk\n",
    "disk = Disk()\n",
    "\n",
    "\n",
    "class MyPillow():\n",
    "    def read_image_from_bytes_io(self, bytes_io):\n",
    "        return Image.open(bytes_io)\n",
    "\n",
    "    def save_image_to_file_path(self, image, file_path):\n",
    "        \"\"\"\n",
    "        foamat = [jpeg, png]\n",
    "        \"\"\"\n",
    "        image.save(file_path)\n",
    "\n",
    "    def get_image_bytes_size(self, image):\n",
    "        image = image.convert('RGB')\n",
    "        out = BytesIO()\n",
    "        image.save(out, format=\"jpeg\")\n",
    "        return out.tell()\n",
    "\n",
    "    def decrease_the_size_of_an_image(self, image, quality=None):\n",
    "        image = image.convert('RGB')\n",
    "        out = BytesIO()\n",
    "        if quality is None:\n",
    "            image.save(out, format=\"jpeg\")\n",
    "        else:\n",
    "            image.save(out, format=\"jpeg\", optimize=True, quality=quality)\n",
    "        out.seek(0)\n",
    "        return out\n",
    "    \n",
    "    def get_file_size(self, path: str, level: str = \"B\", bytes_size: int = None) -> int:\n",
    "        \"\"\"\n",
    "        Get file size in the unit of  B, KB, MB.\n",
    "        Parameters\n",
    "        ----------\n",
    "        path: string\n",
    "            the file path\n",
    "        level: string\n",
    "            B, KB, or MB\n",
    "        bytes_size: int\n",
    "            a number represent the file size in bytes level\n",
    "        \"\"\"\n",
    "        if bytes_size is None:\n",
    "            path = self._expand_user(path)\n",
    "            file = Path(path)\n",
    "            assert file.exists(), f\"{path} is not exist!\"\n",
    "            bytes_size = file.stat().st_size\n",
    "        if level == \"B\":\n",
    "            return int('{:.0f}'.format(bytes_size))\n",
    "        elif level == \"KB\":\n",
    "            return int('{:.0f}'.format(bytes_size / float(1 << 10)))\n",
    "        elif level == \"MB\":\n",
    "            return int('{:.0f}'.format(bytes_size / float(1 << 20)))\n",
    "\n",
    "\n",
    "    def force_decrease_image_file_size(self, image, limit_in_kb=\"1024\"):\n",
    "        \"\"\"\n",
    "        :param image: PIL image\n",
    "        :param limit: kb\n",
    "        :return: bytes_io\n",
    "        \"\"\"\n",
    "        image = image.convert('RGB')\n",
    "        OK = False\n",
    "        quality = 100\n",
    "        out = BytesIO()\n",
    "        while (OK is False):\n",
    "            out = BytesIO()\n",
    "            image.save(out, format=\"jpeg\", optimize=True, quality=quality)\n",
    "            size = self.get_file_size(path=None, bytes_size=out.tell(), level=\"KB\")\n",
    "            quality -= 10\n",
    "            if size <= limit_in_kb or quality <= 10:\n",
    "                OK = True\n",
    "        out.seek(0)\n",
    "        return out\n",
    "\n",
    "myPillow = MyPillow()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import base64\n",
    "import re\n",
    "\n",
    "def myCompress(base64string):\n",
    "    try:\n",
    "        image = myPillow.read_image_from_bytes_io(BytesIO(base64.b64decode(base64string.split(\",\")[1])))\n",
    "        new_image = myPillow.force_decrease_image_file_size(image, 10)\n",
    "        return base64.b64encode(new_image.read())\n",
    "    except Exception as e:\n",
    "        print(base64string)\n",
    "        raise(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import json\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## export data to json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "db_file = os.path.abspath(\"./mine.db\")\n",
    "\n",
    "class Self():\n",
    "    pass\n",
    "\n",
    "self = Self()\n",
    "self.SQL_DATA_FILE = db_file\n",
    "if not os.path.exists(self.SQL_DATA_FILE):\n",
    "    print(f\"SQL doesn't exist: {self.SQL_DATA_FILE}\")\n",
    "    print(f\"We'll create a new one!\")\n",
    "self._sql_conn = sqlite3.connect(self.SQL_DATA_FILE, check_same_thread=False)\n",
    "self._sql_cursor = self._sql_conn.cursor()\n",
    "\n",
    "days = []\n",
    "for row in self._sql_cursor.execute('SELECT * FROM messages ORDER BY date'):\n",
    "    if \" \" in row[0]:\n",
    "        if \".\" in row[0]:\n",
    "            date = row[0].split(\".\")[0]\n",
    "        else:\n",
    "            date = row[0]\n",
    "        row = [date, row[1], row[2]]\n",
    "        \n",
    "        #images = json.loads(row[2])\n",
    "        #new_image_bytes_list = [myCompress(image) for image in images]\n",
    "        #new_stringImage_list = [str(base64.b64encode(i))[2:-1] for i in new_image_bytes_list]\n",
    "        #row[2] = json.dumps(new_stringImage_list)\n",
    "        #print(len(stringImage), len(new_stringImage))\n",
    "        row[2] = json.dumps([element.split(\",\")[1] for element in json.loads(row[2])])\n",
    "        days.append({\n",
    "            \"date\": row[0],\n",
    "            \"content\": row[1],\n",
    "            \"images\": row[2]\n",
    "        })\n",
    "\n",
    "text = json.dumps(days, indent=4)\n",
    "with open(\"mine.json\", \"w\") as f:\n",
    "    f.write(text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'date': '2021-08-21 10:43:23',\n",
       " 'content': 'The more I work for my company. \\n\\nThe more I feel offline APP is gonna be dying (hard to make profit from).',\n",
       " 'images': '[]'}"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "days[-1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## export data to database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(\"mine.json\", \"r\") as f:\n",
    "    text = f.read()\n",
    "days = json.loads(text)\n",
    "days = [[day[0]] + day[2:] for day in days]\n",
    "\n",
    "#days[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "!rm mine.db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL doesn't exist: ./mine.db\n",
      "We'll create a new one!\n"
     ]
    }
   ],
   "source": [
    "class Self():\n",
    "    pass\n",
    "\n",
    "self = Self()\n",
    "self.SQL_DATA_FILE = \"./mine.db\"\n",
    "if not os.path.exists(self.SQL_DATA_FILE):\n",
    "    print(f\"SQL doesn't exist: {self.SQL_DATA_FILE}\")\n",
    "    print(f\"We'll create a new one!\")\n",
    "self._sql_conn = sqlite3.connect(self.SQL_DATA_FILE, check_same_thread=False)\n",
    "self._sql_cursor = self._sql_conn.cursor()\n",
    "self._sql_cursor.execute('''CREATE TABLE IF NOT EXISTS messages\n",
    "        (date TEXT, content TEXT, images TEXT)''')\n",
    "\n",
    "for day in days:\n",
    "    self._sql_cursor.execute(\"INSERT INTO messages VALUES (?,?,?)\", day)\n",
    "    self._sql_conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}